using DbFactory;
using System;
using System.Collections.Generic;
using System.Linq;

namespace SharpConsoleApp
{
    public class QueryExamples
    {
        private readonly IDatabaseConnection _connection;

        public QueryExamples(IDatabaseConnection connection)
        {
            _connection = connection;
        }

        public void RunAllExamples()
        {
            Console.WriteLine("=== 开始运行查询示例 ===");

            // 1. 基本查询示例
            BasicQueryExample();

            // 2. 字符串操作查询示例
            StringOperationExample();

            // 3. IN 查询示例
            InQueryExample();

            // 4. 复杂条件组合示例
            ComplexConditionExample();

            // 5. 连接查询示例
            JoinQueryExample();

            // 6. 更新示例
            UpdateExample();

            // 7. 批量操作示例
            BatchOperationExample();

            Console.WriteLine("=== 查询示例运行完成 ===");
        }

        private void BasicQueryExample()
        {
            Console.WriteLine("\n1. 基本查询示例：");
            
            // 查询年龄大于18的用户
            var adults = _connection.QueryList<PersonOne>(p => p.Age > 18);
            Console.WriteLine($"年龄大于18的用户数量: {adults.Count()}");

            // 查询特定状态的用户
            var activeUsers = _connection.QueryList<PersonOne>(p => p.Status == "Active");
            Console.WriteLine($"活跃用户数量: {activeUsers.Count()}");
        }

        private void StringOperationExample()
        {
            Console.WriteLine("\n2. 字符串操作查询示例：");

            // Contains 查询
            var nameContains = _connection.QueryList<PersonOne>(p => p.personName.Contains("John"));
            Console.WriteLine($"名字包含'John'的用户数量: {nameContains.Count()}");

            // StartsWith 查询
            var nameStartsWith = _connection.QueryList<PersonOne>(p => p.personName.StartsWith("J"));
            Console.WriteLine($"名字以'J'开头的用户数量: {nameStartsWith.Count()}");

            // EndsWith 查询
            var nameEndsWith = _connection.QueryList<PersonOne>(p => p.personName.EndsWith("n"));
            Console.WriteLine($"名字以'n'结尾的用户数量: {nameEndsWith.Count()}");
        }

        private void InQueryExample()
        {
            Console.WriteLine("\n3. IN 查询示例：");

            // 使用数组的 IN 查询
            var ids = new[] { 1, 2, 3, 4, 5 };
            var usersByIds = _connection.QueryList<PersonOne>(p => ids.Contains(p.Id));
            Console.WriteLine($"ID在1-5范围内的用户数量: {usersByIds.Count()}");

            // 使用列表的 IN 查询
            var statuses = new List<string> { "Active", "Pending" };
            var usersByStatus = _connection.QueryList<PersonOne>(p => statuses.Contains(p.Status));
            Console.WriteLine($"状态为Active或Pending的用户数量: {usersByStatus.Count()}");
        }

        private void ComplexConditionExample()
        {
            Console.WriteLine("\n4. 复杂条件组合示例：");

            // 组合多个条件
            var complexQuery = _connection.QueryList<PersonOne>(p =>
                p.Age > 18 &&
                (p.personName.Contains("John") || p.personName.StartsWith("J")) &&
                new[] { "Active", "Pending" }.Contains(p.Status)
            );
            Console.WriteLine($"复杂条件查询结果数量: {complexQuery.Count()}");
        }

        private void JoinQueryExample()
        {
            Console.WriteLine("\n5. 连接查询示例：");

            // 假设我们有一个Order类
            _connection.CreateTable<Order>();

            // 连接查询
            var joinResults = _connection.QueryJoin<PersonOne, Order, PersonOrderDto>(
                (p, o) => p.Id == o.PersonId,  // 连接条件
                (p, o) => new PersonOrderDto    // 选择字段
                {
                    PersonId = p.Id,
                    PersonName = p.personName,
                    OrderId = o.Id,
                    OrderDate = o.OrderDate
                },
                (p, o) => p.Age > 18 && o.OrderDate > DateTime.Now.AddDays(-30)  // WHERE 条件
            );

            Console.WriteLine($"连接查询结果数量: {joinResults.Count()}");
        }

        private void UpdateExample()
        {
            Console.WriteLine("\n6. 更新示例：");

            // 1. 使用ID更新
            Console.WriteLine("使用ID更新示例：");
            _connection.Update<PersonOne>(
                p => new PersonOne 
                { 
                    personName = "Updated Name",
                    Age = 30,
                    Status = "Updated"
                },
                "1"  // ID值
            );

            // 2. 使用条件更新
            Console.WriteLine("使用条件更新示例：");
            _connection.Update<PersonOne>(
                p => new PersonOne 
                { 
                    Status = "Inactive",
                    personDetail = "Updated by condition"
                },
                p => p.Age > 25 && p.Status == "Active"
            );

            // 验证更新结果
            var updatedPerson = _connection.QueryFirst<PersonOne>("1");
            Console.WriteLine($"更新后的用户信息：");
            Console.WriteLine($"Name: {updatedPerson.personName}");
            Console.WriteLine($"Age: {updatedPerson.Age}");
            Console.WriteLine($"Status: {updatedPerson.Status}");
            Console.WriteLine($"Detail: {updatedPerson.personDetail}");
        }

        private void BatchOperationExample()
        {
            Console.WriteLine("\n7. 批量操作示例：");

            // 1. 批量插入示例
            Console.WriteLine("批量插入示例：");
            var personsToInsert = new List<PersonOne>
            {
                new PersonOne
                {
                    Id = 2,
                    personName = "Alice Smith",
                    personDetail = "Test person 2",
                    Age = 28,
                    Status = "Active",
                    CreateTime = DateTime.Now
                },
                new PersonOne
                {
                    Id = 3,
                    personName = "Bob Johnson",
                    personDetail = "Test person 3",
                    Age = 35,
                    Status = "Pending",
                    CreateTime = DateTime.Now
                }
            };

            _connection.BatchInsert(personsToInsert);
            Console.WriteLine($"已批量插入 {personsToInsert.Count} 条记录");

            // 2. 批量更新示例 - 使用条件
            Console.WriteLine("\n批量更新示例（使用条件）：");
            var personsToUpdate = _connection.QueryList<PersonOne>(p => p.Age > 25).ToList();
            _connection.BatchUpdate(
                personsToUpdate,
                p => new PersonOne { Status = "Updated", personDetail = "Batch updated" },
                p => p.Age > 25
            );
            Console.WriteLine($"已批量更新 {personsToUpdate.Count} 条记录");

            // 3. 批量更新示例 - 使用ID
            Console.WriteLine("\n批量更新示例（使用ID）：");
            var personsToUpdateById = new List<PersonOne>
            {
                new PersonOne
                {
                    Id = 2,
                    personName = "Alice Smith Updated",
                    Age = 29,
                    Status = "Inactive"
                },
                new PersonOne
                {
                    Id = 3,
                    personName = "Bob Johnson Updated",
                    Age = 36,
                    Status = "Archived"
                }
            };

            _connection.BatchUpdate(personsToUpdateById);
            Console.WriteLine($"已批量更新 {personsToUpdateById.Count} 条记录");

            // 验证更新结果
            var allPersons = _connection.QueryList<PersonOne>();
            Console.WriteLine("\n更新后的所有用户信息：");
            foreach (var person in allPersons)
            {
                Console.WriteLine($"ID: {person.Id}");
                Console.WriteLine($"Name: {person.personName}");
                Console.WriteLine($"Age: {person.Age}");
                Console.WriteLine($"Status: {person.Status}");
                Console.WriteLine($"Detail: {person.personDetail}");
                Console.WriteLine("---");
            }
        }
    }

    // 用于连接查询的DTO类
    public class PersonOrderDto
    {
        public int PersonId { get; set; }
        public string PersonName { get; set; }
        public int OrderId { get; set; }
        public DateTime OrderDate { get; set; }
    }

    // 订单类
    public class Order
    {
        public int Id { get; set; }
        public int PersonId { get; set; }
        public DateTime OrderDate { get; set; }
        public decimal Amount { get; set; }
        public string Status { get; set; }
    }

} 